﻿/*
 * This software is the confidential and proprietary
 * information of NetBay
 * Such confidential information should not be disclosed
 * NetBay copyright(c) 2010
 */

#region Using

using System;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Data.OracleClient;
using System.Data.SqlClient;
using Microsoft.Practices.EnterpriseLibrary.Data;
using NetBay.Core.DataAccess;
using NetBay.Core.Extensions;
using NetBay.Core.Log;
using Rio.Framework.RioManager;
using System.Collections.Generic;

#endregion

namespace Rio.Doc.DataAccess
{
    public static class DA_User
    {
        #region Enum
        /// <summary>
        /// List of DB functions to access data
        /// </summary>
        public enum DbFunction
        {
            [DescriptionAttribute("")]
            None,
            [DescriptionAttribute("P_USER_Get_ByLoginPassword")]
            GetByLoginPassword,
            [DescriptionAttribute("P_USER_Get_ByLogin")]
            GetByLogin,
            [DescriptionAttribute("P_USER_List")]
            GetAllUser,
            [DescriptionAttribute("P_USER_Get_ById")]
            GetById,
            [DescriptionAttribute("P_USER_GetMail_ByGroupName")]
            GetAllEmailUserByGroupName,
            [DescriptionAttribute("P_HISTORIC_Add")]
            AddUserAction,
            [DescriptionAttribute("P_USER_Get_ByFolderTypeId")]
            GetUsersByFolderTypeId,
            [DescriptionAttribute("P_USER_Get_ByFolderId")]
            GetUsersByFolderId,
            [DescriptionAttribute("P_AUTORISATIONS_DOC_Add")]
            AddAuthorisationsDoc,
            [DescriptionAttribute("P_AUTORISATIONS_DOC_Delete")]
            DeleteAuthorisationsDoc,
            [DescriptionAttribute("P_USER_Password_Update")]
            UpdateUserPassword,
            [DescriptionAttribute("P_ADMIN_User_Add")]
            AddUser,
            [DescriptionAttribute("P_ADMIN_User_Upd")]
            UpdateUser,
            [DescriptionAttribute("P_ADMIN_User_Del")]
            DeleteUser,
            [DescriptionAttribute("P_ADMIN_User_Group_Del")]
            RemoveGroupToUser,
            [DescriptionAttribute("P_ADMIN_User_Group_Add")]
            AddGroupToUser,
            [DescriptionAttribute("P_USER_Get_ByGroupId")]
            GetUsersByGroupId,
        }
        #endregion

        /// <summary>
        /// Get the user by his login and password
        /// </summary>
        /// <param name="p_sLogin">Login of the user</param>
        /// <param name="p_sPassword">Password of the user</param>
        /// <returns>A datareader that contains the user informations</returns>
        public static IDataReader Find(string p_sLogin, string p_sPassword)
        {
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.GetByLoginPassword.szGetDescription());
            db.AddInParameter(dbCommand, "p_userLogin", DbType.String, p_sLogin);
            db.AddInParameter(dbCommand, "p_userPassword", DbType.String, p_sPassword);

            return db.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// Get the number of users declared
        /// </summary>
        /// <returns>User declared</returns>
        public static IDataReader GetNbUserDeclared()
        {
            var db = DbHelper.DataBase;

            var dbCommand = db.GetSqlStringCommand("select count(ruser_no) as NbUser from  r_user");

            return db.ExecuteReader(dbCommand);
        }


        /// <summary>
        /// Add a user's action
        /// </summary>
        /// <param name="p_lUserId">The user identifier</param>
        /// <param name="p_sAction">the user's action</param>
        /// <param name="p_lFolderTypeId">the folder type identifier</param>
        /// <param name="p_lFolderId">The P_L folder id.</param>
        /// <param name="p_sDocumentId">the document Identifier</param>
        /// <param name="p_sFileName">The original filename</param>
        /// <param name="p_sFolderIdDoc">The P_S folder id doc.</param>
        /// <param name="p_sIndexes">The P_S indexes.</param>
        /// <param name="p_iCpt">The p_i CPT.</param>
        /// <param name="p_bSave">if set to <c>true</c> [P_B save].</param>
        /// <param name="p_iCptResult">The p_i CPT result.</param>
        /// <param name="p_iCptIndexed">The p_i CPT indexed.</param>
        /// <param name="p_sIndexName">Name of the P_S index.</param>
        public static void AddUserAction(
            long p_lUserId,
            string p_sAction,
            long p_lFolderTypeId,
            long p_lFolderId,
            string p_sDocumentId,
            string p_sFileName,
            string p_sFolderIdDoc = "",
            string p_sIndexes = "",
            int p_iCpt = 0,
            bool p_bSave = false,
            int p_iCptResult = 0,
            int p_iCptIndexed = 0,
            string p_sIndexName = "")
        {
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.AddUserAction.szGetDescription(), false);
            db.AddInParameter(dbCommand, "p_lUserId", DbType.Int64, p_lUserId);
            db.AddInParameter(dbCommand, "p_sAction", DbType.String, p_sAction);
            db.AddInParameter(dbCommand, "p_lFolderTypeId", DbType.Int32, p_lFolderTypeId);
            db.AddInParameter(dbCommand, "p_lFolderId", DbType.Int32, p_lFolderId);
            db.AddInParameter(dbCommand, "p_sDocumentId", DbType.String, p_sDocumentId);
            db.AddInParameter(dbCommand, "p_sFileName", DbType.String, p_sFileName);

            db.AddInParameter(dbCommand, "p_sFolderIdDoc", DbType.String, p_sFolderIdDoc);
            db.AddInParameter(dbCommand, "p_iCpt", DbType.Int32, p_iCpt);
            db.AddInParameter(dbCommand, "p_sIndexes", DbType.AnsiString, p_sIndexes);
            db.AddInParameter(dbCommand, "p_bSave", DbType.Boolean, p_bSave);
            db.AddInParameter(dbCommand, "p_iCptResult", DbType.Int32, p_iCptResult);
            db.AddInParameter(dbCommand, "p_iCptIndexed", DbType.Int32, p_iCptIndexed);
            db.AddInParameter(dbCommand, "p_sIndexName", DbType.String, p_sIndexName);
            db.ExecuteNonQuery(dbCommand);
        }



        /// <summary>
        /// Get the user by his login 
        /// </summary>
        /// <param name="p_sLogin">Login of the user</param>
        /// <returns>A datareader that contains the user informations</returns>
        public static IDataReader Find(string p_sLogin)
        {
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.GetByLogin.szGetDescription());
            db.AddInParameter(dbCommand, "p_userLogin", DbType.String, p_sLogin);

            return db.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// Get the user by his login 
        /// </summary>
        /// <param name="p_sLogin">Login of the user</param>
        /// <returns>A datareader that contains the user informations</returns>
        public static IDataReader GetAll()
        {
            var db = DbHelper.DataBase;
            var dbCommand = db.GetNewCommand(DbFunction.GetAllUser.szGetDescription());
            return db.ExecuteReader(dbCommand);
        }

        public static IDataReader GetUsersByFolderTypeId(long p_lFolderTypeId)
        {
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.GetUsersByFolderTypeId.szGetDescription());
            db.AddInParameter(dbCommand, "p_lFolderTypeId", DbType.Int64, p_lFolderTypeId);

            return db.ExecuteReader(dbCommand);
        }

        public static IDataReader GetUserById(long p_userId)
        {
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.GetById.szGetDescription());
            db.AddInParameter(dbCommand, "p_userId", DbType.Int64, p_userId);

            return db.ExecuteReader(dbCommand);
        }

        public static IDataReader GetUsersByFolderId(string p_lFolderId)
        {
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.GetUsersByFolderId.szGetDescription());
            db.AddInParameter(dbCommand, "p_sFolderId", DbType.String, p_lFolderId);

            return db.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// Gets the users by group id.
        /// </summary>
        /// <param name="groupId">The group id.</param>
        /// <returns></returns>
        public static IDataReader GetUsersByGroupId(long groupId)
        {
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.GetUsersByGroupId.szGetDescription());
            db.AddInParameter(dbCommand, "p_groupId", DbType.Int64, groupId);

            return db.ExecuteReader(dbCommand);
        }

        public static void AddAuthorisationsDoc(string p_sFolderId, long p_lUserId)
        {
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.AddAuthorisationsDoc.szGetDescription(), false);
            db.AddInParameter(dbCommand, "p_sFolderId", DbType.String, p_sFolderId);
            db.AddInParameter(dbCommand, "p_lUserId", DbType.Int64, p_lUserId);
            db.ExecuteNonQuery(dbCommand);
        }

        public static void DeleteAuthorisationsDoc(string p_sFolderId, long p_lUserId)
        {
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.DeleteAuthorisationsDoc.szGetDescription(), false);
            db.AddInParameter(dbCommand, "p_sFolderId", DbType.String, p_sFolderId);
            db.AddInParameter(dbCommand, "p_lUserId", DbType.Int64, p_lUserId);
            db.ExecuteNonQuery(dbCommand);
        }

        /// <summary>
        /// Get all users email from a group
        /// </summary>
        /// <param name="p_szGroupName">Group Name</param>
        /// <returns>A DataReader that contains all user email from a group</returns>
        public static IDataReader GetAllEmailUserByGroupName(string p_szGroupName)
        {
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.GetAllEmailUserByGroupName.szGetDescription());
            db.AddInParameter(dbCommand, "p_GroupName", DbType.String, p_szGroupName);

            return db.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// Updates the user password.
        /// </summary>
        /// <param name="p_lUserId">UserID</param>
        /// <param name="p_sOldPassword">Old Password</param>
        /// <param name="p_sNewPassword">New Password</param>
        /// <returns></returns>
        public static IDataReader UpdateUserPassword(long p_lUserId, string p_sOldPassword, string p_sNewPassword)
        {
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.UpdateUserPassword.szGetDescription());
            db.AddInParameter(dbCommand, "p_lUserId", DbType.Int64, p_lUserId);
            db.AddInParameter(dbCommand, "p_sOldPassword", DbType.String, p_sOldPassword);
            db.AddInParameter(dbCommand, "p_sNewPassword", DbType.String, p_sNewPassword);

            return db.ExecuteReader(dbCommand);
        }


        /// <summary>
        /// Adds the user.
        /// </summary>
        /// <param name="login">The login.</param>
        /// <param name="password">The password.</param>
        /// <param name="email">The email.</param>
        /// <param name="firstName">The first name.</param>
        /// <param name="name">The name.</param>
        /// <returns></returns>
        public static bool AddUser(string login, string password, string email, string firstName, string name)
        {
            try
            {
                LogManager.Instance().LogDebugFormat("Calling SP: '{0}'", DbFunction.AddUser.szGetDescription());
                LogManager.Instance().LogDebug("(login<" + login + ">password<" + password + ">email<" + email + ">firstName<" + firstName + ">name<" + name + ">)");
                var db = DbHelper.DataBase;

                var dbCommand = db.GetNewCommand(DbFunction.AddUser.szGetDescription(), false);
                db.AddInParameter(dbCommand, "p_sLogin", DbType.String, login);
                db.AddInParameter(dbCommand, "p_sPassword", DbType.String, password);
                db.AddInParameter(dbCommand, "p_sEMail", DbType.String, email);
                db.AddInParameter(dbCommand, "p_sFirstName", DbType.String, firstName);
                db.AddInParameter(dbCommand, "p_sName", DbType.String, name);

                db.ExecuteNonQuery(dbCommand);
                return true;
            }
            catch (Exception ex)
            {
                LogManager.Instance().LogError("AddUser exception[" + ex.Message + "]");
            }
            return false;
        }

        /// <summary>
        /// Adds the user.
        /// </summary>
        /// <param name="login">The login.</param>
        /// <param name="password">The password.</param>
        /// <param name="email">The email.</param>
        /// <param name="firstName">The first name.</param>
        /// <param name="name">The name.</param>
        /// <param name="groupIds">The group ids.</param>
        /// <returns></returns>
        public static StoredProcedureResult AddUser(string login, string password, string email, string firstName, string name, List<long> groupIds = null)
        {
            LogManager.Instance().LogDebugFormat("Calling SP: '{0}'", DbFunction.AddUser.szGetDescription());
            LogManager.Instance().LogDebug("(login<" + login + ">password<" + password + ">email<" + email +
                                           ">firstName<" + firstName + ">name<" + name + ">)");

            var db = DbHelper.DataBase;
            DbTransaction transaction = null;
            StoredProcedureResult result = StoredProcedureResult.GetDefault();
            using (DbConnection connection_db_b = db.CreateConnection())
            {
                try
                {
                    connection_db_b.Open();
                    transaction = connection_db_b.BeginTransaction();

                    //Add user
                    var dbCommand = db.GetNewCommand(DbFunction.AddUser.szGetDescription(), false);
                    db.AddInParameter(dbCommand, "p_sLogin", DbType.String, login);
                    db.AddInParameter(dbCommand, "p_sPassword", DbType.String, password);
                    db.AddInParameter(dbCommand, "p_sEMail", DbType.String, email);
                    db.AddInParameter(dbCommand, "p_sFirstName", DbType.String, firstName);
                    db.AddInParameter(dbCommand, "p_sName", DbType.String, name);
                    result = db.ExecuteAndGetResult(dbCommand, transaction);
                    if (result.HasError)
                    {
                        transaction.Rollback();
                        return result;
                    }

                    //Add groups to user
                    long newUserId = result.NewIdentifier;
                    if (groupIds != null && groupIds.Count > 0)
                    {
                        foreach (var groupId in groupIds)
                        {
                            dbCommand = db.GetNewCommand(DbFunction.AddGroupToUser.szGetDescription(), false);
                            db.AddInParameter(dbCommand, "p_lUserId", DbType.Int64, newUserId);
                            db.AddInParameter(dbCommand, "p_lGroupId", DbType.Int64, groupId);
                            result = db.ExecuteAndGetResult(dbCommand, transaction);
                            if (result.HasError)
                            {
                                transaction.Rollback();
                                return result;
                            }
                        }
                    }


                    transaction.Commit();
                    dbCommand.Dispose();

                }
                catch (Exception ex)
                {
                    LogManager.Instance().LogError("Exception -> " + Environment.NewLine + ex.Message);
                    if (transaction != null)
                    {
                        transaction.Rollback();
                        LogManager.Instance().LogInfo("Rollback() executed...");
                    }
                    return new StoredProcedureResult(ResultCode.Error, ex.Message);
                }
            }

            return new StoredProcedureResult(ResultCode.Executed);
        }

        /// <summary>
        /// Updates the user.
        /// </summary>
        /// <param name="userId">The user id.</param>
        /// <param name="login">The login.</param>
        /// <param name="password">The password.</param>
        /// <param name="email">The email.</param>
        /// <param name="firstName">The first name.</param>
        /// <param name="name">The name.</param>
        /// <param name="groupIds">The group ids.</param>
        /// <returns></returns>
        public static StoredProcedureResult UpdateUser(long userId, string login, string password, string email, string firstName, string name, List<long> groupIds = null)
        {
            LogManager.Instance().LogDebugFormat("Calling SP: '{0}'", DbFunction.AddUser.szGetDescription());
            LogManager.Instance().LogDebug("(login<" + login + ">password<" + password + ">email<" + email +
                                           ">firstName<" + firstName + ">name<" + name + ">)");

            var db = DbHelper.DataBase;
            DbTransaction transaction = null;
            StoredProcedureResult result = StoredProcedureResult.GetDefault();
            using (DbConnection connection_db_b = db.CreateConnection())
            {
                try
                {
                    connection_db_b.Open();
                    transaction = connection_db_b.BeginTransaction();

                    //Add user
                    var dbCommand = db.GetNewCommand(DbFunction.UpdateUser.szGetDescription(), false);
                    db.AddInParameter(dbCommand, "p_lUserId", DbType.Int64, userId);
                    db.AddInParameter(dbCommand, "p_sLogin", DbType.String, login);
                    db.AddInParameter(dbCommand, "p_sPassword", DbType.String, password);
                    db.AddInParameter(dbCommand, "p_sEMail", DbType.String, email);
                    db.AddInParameter(dbCommand, "p_sFirstName", DbType.String, firstName);
                    db.AddInParameter(dbCommand, "p_sName", DbType.String, name);
                    result = db.ExecuteAndGetResult(dbCommand, transaction);
                    if (result.HasError)
                    {
                        transaction.Rollback();
                        return result;
                    }

                    //Remove all groups 
                    dbCommand = db.GetNewCommand(DbFunction.RemoveGroupToUser.szGetDescription());
                    db.AddInParameter(dbCommand, "p_lUserId", DbType.Int64, userId);
                    result = db.ExecuteAndGetResult(dbCommand, transaction);
                    if (result.HasError)
                    {
                        transaction.Rollback();
                        return result;
                    }

                    //Add groups
                    if (groupIds != null)
                    {
                        foreach (var groupId in groupIds)
                        {
                            dbCommand = db.GetNewCommand(DbFunction.AddGroupToUser.szGetDescription(), false);
                            db.AddInParameter(dbCommand, "p_lUserId", DbType.Int64, userId);
                            db.AddInParameter(dbCommand, "p_lGroupId", DbType.Int64, groupId);
                            result = db.ExecuteAndGetResult(dbCommand, transaction);
                            if (result.HasError)
                            {
                                transaction.Rollback();
                                return result;
                            }
                        }
                    }


                    transaction.Commit();
                    dbCommand.Dispose();

                }
                catch (Exception ex)
                {
                    LogManager.Instance().LogError("Exception -> " + Environment.NewLine + ex.Message);
                    if (transaction != null)
                    {
                        transaction.Rollback();
                        LogManager.Instance().LogInfo("Rollback() executed...");
                    }
                    return new StoredProcedureResult(ResultCode.Error, ex.Message);
                }
            }

            return new StoredProcedureResult(ResultCode.Executed);
        }

        /// <summary>
        /// Deletes the user.
        /// </summary>
        /// <param name="userId">The user id.</param>
        /// <returns></returns>
        public static StoredProcedureResult DeleteUser(long userId)
        {
            var db = DbHelper.DataBase;

            LogManager.Instance().LogDebugFormat("Calling SP: '{0}'", DbFunction.DeleteUser.szGetDescription());
            var dbCommand = db.GetNewCommand(DbFunction.DeleteUser.szGetDescription());
            db.AddInParameter(dbCommand, "p_lUserId", DbType.Int64, userId);
            return db.ExecuteAndGetResult(dbCommand);
        }
    }
}
